var _db;

function openDB()
{
_db = openDatabaseSync("studyAidDB", "1.0", "Study Aid Database", 1000000);
}

function createTable()
{
    openDB();
    _db.transaction(
                function(tx){
                    tx.executeSql("CREATE TABLE IF NOT EXISTS topic (topicName TEXT, checked INTEGER, topicID INTEGER PRIMARY KEY AUTOINCREMENT)");
                    tx.executeSql("CREATE TABLE IF NOT EXISTS note (noteName TEXT, noteBody TEXT, checked INTEGER, topicID INTEGER, noteID INTEGER PRIMARY KEY AUTOINCREMENT)");
                    tx.executeSql("CREATE TABLE IF NOT EXISTS quizTopic (quizTopicName TEXT, checked INTEGER, quizTopicID INTEGER PRIMARY KEY AUTOINCREMENT)");
                    tx.executeSql("CREATE TABLE IF NOT EXISTS question (question TEXT, answer TEXT, checked INTEGER, quizTopicID INTEGER, questionID INTEGER PRIMARY KEY AUTOINCREMENT)");
                }
                )
}

//************************************************************************************************************************//
function readTopics(model)
{
    model.clear()
    //Set all checked state to false
    _db.transaction(
                function(tx){
                    tx.executeSql("UPDATE topic SET checked = 0");
                }
                )
    _db.readTransaction(
                function(tx){
                    var rs = tx.executeSql("SELECT * FROM topic");
                    for (var i=0; i< rs.rows.length; i++) {
                        model.append(rs.rows.item(i))
                    }
                }
                )
}

function addTopic(topicName)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("INSERT INTO topic (topicName, checked) VALUES (?, 0)", topicName);
                }
                )
}

function updateTopicChecked(topic)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("UPDATE topic SET checked = ? WHERE topicID = ?", [topic.checked ? 1 : 0, topic.topicID]);
                }
                )
}

function updateTopicName(topicName)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("UPDATE topic SET topicName = ? WHERE checked = 1",topicName);
                }
                )
}

function deleteTopics()
{
    _db.readTransaction(
                function(tx){
                    var rs = tx.executeSql("SELECT * FROM topic WHERE checked = 1");
                    for (var i=0; i< rs.rows.length; i++) {
                        deleteTopic(rs.rows.item(i)["topicID"]);
                    }
                }
                )
}

function deleteTopic(topicID){
    _db.transaction(
                function(tx){
                    tx.executeSql("DELETE FROM note WHERE topicID = ?", topicID);
                    tx.executeSql("DELETE FROM topic WHERE topicID = ?", topicID);
                }
                )
}

//************************************************************************************************************************//
function readNotes(model, topicID)
{
    model.clear()
    //Set all checked state to false
    _db.transaction(
                function(tx){
                    tx.executeSql("UPDATE note SET checked = 0");
                }
                )
    _db.readTransaction(
                function(tx){
                    var rs = tx.executeSql("SELECT * FROM note where topicID = ?", topicID);
                    for (var i=0; i< rs.rows.length; i++) {
                        model.append(rs.rows.item(i))
                    }
                }
                )
}
//function addNote(topicID, userInput)
//{
//    _db.transaction(
//                function(tx){
//                    tx.executeSql("INSERT INTO note(noteName, noteBody, checked, topicID) VALUES (?, '', 0, ?)", [userInput, topicID]);
//                }
//                )
//}

function addNote(noteName, noteBody, topicID)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("INSERT INTO note(noteName, noteBody, checked, topicID) VALUES (?, ?, 0, ?)", [noteName, noteBody, topicID]);
                }
                )
}

function updateNoteChecked(note)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("UPDATE note SET checked = ? WHERE noteID = ?", [note.checked ? 1 : 0, note.noteID]);
                }
                )
}

function deleteNotes()
{
    _db.transaction(
                function(tx){
                    tx.executeSql("DELETE FROM note WHERE checked = 1");
                }
                )
}

function deleteNote(noteID)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("DELETE FROM note WHERE noteID = ?", noteID);
                }
                )
}

function updateNote(noteName, noteBody, noteID)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("UPDATE note SET noteName = ?, noteBody  = ? WHERE noteID = ?",[noteName, noteBody, noteID]);
                }
                )
}

//************************************************************************************************************************//
function readQuizTopics(model)
{
    model.clear()
    //Set all checked state to false
    _db.transaction(
                function(tx){
                    tx.executeSql("UPDATE quizTopic SET checked = 0");
                }
                )
    _db.readTransaction(
                function(tx){
                    var rs = tx.executeSql("SELECT * FROM quizTopic");
                    for (var i=0; i< rs.rows.length; i++) {
                        model.append(rs.rows.item(i))
                    }
                }
                )
}

function addQuizTopic(quizTopicName)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("INSERT INTO quizTopic (quizTopicName, checked) VALUES (?, 1)", quizTopicName);
                }
                )
}

function updateQuizTopicChecked(quizTopic)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("UPDATE quizTopic SET checked = ? WHERE quizTopicID = ?", [quizTopic.checked ? 1 : 0, quizTopic.quizTopicID]);
                }
                )
}

function updateQuizTopicName(userInput)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("UPDATE quizTopic SET quizTopicName = ? WHERE checked = 1", userInput);
                }
                )
}


function deleteQuizTopics()
{
    _db.readTransaction(
                function(tx){
                    var rs = tx.executeSql("SELECT * FROM quizTopic WHERE checked = 1");
                    for (var i=0; i< rs.rows.length; i++) {
                        deleteQuizTopic(rs.rows.item(i)["quizTopicID"]);
                    }
                }
                )
}

function deleteQuizTopic(quizTopicID){
    _db.transaction(
                function(tx){
                    tx.executeSql("DELETE FROM question WHERE quizTopicID = ?", quizTopicID);
                    tx.executeSql("DELETE FROM quizTopic WHERE quizTopicID = ?", quizTopicID);
                }
                )
}

//************************************************************************************************************************//
function addQuestion(questionInput, answerInput, quizTopicID)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("INSERT INTO question(question, answer, checked, quizTopicID) VALUES(?, ?, 0, ?)", [questionInput, answerInput, quizTopicID]);
                }
                )
}

function readQuestion(model, quizTopicID)
{
    model.clear()
    //Set all checked state to false
    _db.transaction(
                function(tx){
                    tx.executeSql("UPDATE question SET checked = 0");
                }
                )
    _db.readTransaction(
                function(tx){
                    var rs = tx.executeSql("SELECT * FROM question where quizTopicID = ?", quizTopicID);
                    for (var i=0; i< rs.rows.length; i++) {
                        model.append(rs.rows.item(i))
                    }
                }
                )
}

function updateQuestionChecked(question)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("UPDATE question SET checked = ? WHERE questionID = ?", [question.checked ? 1 : 0, question.questionID]);
                }
                )
}

function deleteQuestions()
{
    _db.transaction(
                function(tx){
                    tx.executeSql("DELETE FROM question WHERE checked = 1");
                }
                )
}

function updateQuestion(questionInput, answerInput, questionID)
{
    _db.transaction(
                function(tx){
                    tx.executeSql("UPDATE question SET question = ?, answer = ? WHERE questionID = ?", [questionInput, answerInput, questionID]);
                }
                )
}

//************************************************************************************************************************//
function countQuestions(quizTopicID){
    openDB();
    var count= 0;
    _db.readTransaction(function(tx){
                            var rs = tx.executeSql("SELECT * FROM question where quizTopicID = ?", quizTopicID);
                            for (var i=0; i< rs.rows.length; i++) {
                            count++;
                            }
                        });
    return count;
}

function readQuestionsForQuiz(quizTopicID, question, ansA, ansB, ansC, ansD)
{
    var questions = new Array();
    var answers = new Array();
    var IDs = new Array();

    openDB();
    _db.readTransaction(
                function(tx){
                    var rs = tx.executeSql("SELECT * FROM question where quizTopicID = ?", quizTopicID);
                    for (var i=0; i< rs.rows.length; i++) {
                        IDs.push(rs.rows.item(i)["questionID"]);

                        questions.push(rs.rows.item(i)["questionID"]);
                        questions[rs.rows.item(i)["questionID"]] = rs.rows.item(i)["question"];

                        answers.push(rs.rows.item(i)["questionID"]);
                        answers[rs.rows.item(i)["questionID"]] = rs.rows.item(i)["answer"];
                    }
                }
                )

//    for (var id3 in IDs){
//        console.debug("Question ID: " + IDs[id3]);
//    }

//    for (var id in questions) {
//        if (questions.hasOwnProperty(id) && id != 0) {
//            console.debug('key is: ' + id + ', value is: ' + questions[id]);
//        }
//    }

//    for (var id2 in answers) {
//        if (answers.hasOwnProperty(id2) && id2 != 0) {
//            console.debug('key is: ' + id2 + ', value is: ' + answers[id2]);
//        }
//    }

    ansA.text = "";
    ansB.text = "";
    ansC.text = "";
    ansD.text = "";
    question.text = "";

    var IDsID = Math.floor(Math.random()*IDs.length);
    var randomID = IDs[IDsID];
    question.text = questions[randomID];
    var ansNum = Math.floor(Math.random()*4);
    if(ansNum == 0){
        ansA.text = answers[randomID];
    }else if (ansNum == 1){
        ansB.text = answers[randomID];
    }else if (ansNum == 2){
        ansC.text = answers[randomID];
    }else if (ansNum == 3){
        ansD.text = answers[randomID];
    }

    var newIDsID = new Array();
    for(var g in IDs){
        if (g != IDsID){
            newIDsID.push(IDs[g]);
        }
    }

    var IDx;
    var randIDx;
    var randIDxList = new Array();
    randIDxList.push(IDsID);
    for(var x = 0; x < newIDsID.length; x++){
        var check;

        do{
        IDx = Math.floor(Math.random()*newIDsID.length);
        randIDx = newIDsID[IDx];

        check = true;
        for(var x = 0; x < randIDxList.length; x++){
            if(randIDxList[x] == randIDx){
                check = false
            }
        }
        }
        while(check == false)

        if(ansA.text == ""){
            ansA.text = answers[randIDx];
        }else if(ansB.text == ""){
            ansB.text = answers[randIDx];
        }else if(ansC.text == ""){
            ansC.text = answers[randIDx];
        }else if (ansD.text == ""){
            ansD.text = answers[randIDx];
        }
        randIDxList.push(randIDx);
    }

    return ansNum;
}
